/*Tables*/
IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SendNoticeStatus') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.SendNoticeStatus
		(
		SendNoticeStatusID int IDENTITY(1,1) NOT NULL,
		SendNoticeStatus nvarchar(200)
		)
	ON [PRIMARY]

	ALTER TABLE dbo.SendNoticeStatus WITH NOCHECK ADD 
		CONSTRAINT [PK_SendNoticeStatus] PRIMARY KEY CLUSTERED 
			(
			SendNoticeStatusID
			)
	ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SendNoticeQueue') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.SendNoticeQueue
		(
		SendNoticeQueueID int IDENTITY(1,1) NOT NULL,
		SiteUrl nvarchar(1000) NOT NULL,
		NewSiteUrl nvarchar(1000) NULL,
		NoticeID int NOT NULL,
		PreservationCriteria int DEFAULT(0) NOT NULL,
		FromAddress nvarchar(500) NOT NULL,
		ExtraCriteriaName nvarchar(500) NULL,
		ExtraCriteriaValue nvarchar(500) NULL,
		Created datetime DEFAULT(GETDATE()) NOT NULL,
		IsBeingProcessed bit DEFAULT(0) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.SendNoticeQueue WITH NOCHECK ADD 
		CONSTRAINT [PK_SendNoticeQueue] PRIMARY KEY CLUSTERED 
		(
		SendNoticeQueueID
		)
	ON [PRIMARY] 
END
ELSE
BEGIN
	-- See if the IsBeingProcessed field is not there
	IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SendNoticeQueue' AND COLUMN_NAME = 'IsBeingProcessed')
	BEGIN
		ALTER TABLE dbo.SendNoticeQueue ADD IsBeingProcessed bit DEFAULT(0) NOT NULL
	END
END
GO

IF NOT EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SendNoticeHistory') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.SendNoticeHistory
		(
		SendNoticeHistoryID int IDENTITY(1,1) NOT NULL,
		SendNoticeQueueID int NOT NULL,
		SendNoticeStatusID int NOT NULL,
		ErrorMessage text NULL,
		SiteUrl nvarchar(1000) NOT NULL,
		NewSiteUrl nvarchar(1000) NULL,
		NoticeID int NOT NULL,
		PreservationCriteria int DEFAULT(0) NOT NULL,
		FromAddress nvarchar(500) NOT NULL,
		SendNoticeCreated datetime NOT NULL,
		SendNoticeQueueStatusUpdated datetime DEFAULT(GETDATE()) NOT NULL
		)
	ON [PRIMARY]

	ALTER TABLE dbo.SendNoticeHistory WITH NOCHECK ADD 
		CONSTRAINT [PK_SendNoticeHistory] PRIMARY KEY NONCLUSTERED 
			(
			SendNoticeHistoryID
			)
	ON [PRIMARY]

	ALTER TABLE dbo.SendNoticeHistory ADD 
		CONSTRAINT [FK_SendNoticeHistory_SendNoticeStatus] FOREIGN KEY 
		(
			SendNoticeStatusID
		) 
		REFERENCES dbo.SendNoticeStatus
		(
			SendNoticeStatusID
		)
END
GO

IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.UrlDictionary') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE dbo.UrlDictionary
GO

CREATE TABLE dbo.UrlDictionary
	(
	UrlDictionaryID bigint IDENTITY(1,1) NOT NULL,
	UrlKey varchar(50) NOT NULL,
	UrlValue varchar(300) NOT NULL,
	NewUrlValue nvarchar(1000) NULL,
	)
ON [PRIMARY]
GO

IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.PreservationNoticeHistoryAttachment') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE dbo.PreservationNoticeHistoryAttachment
GO

IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.PreservationNoticeHistory') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE dbo.PreservationNoticeHistory
GO

CREATE TABLE dbo.PreservationNoticeHistory
	(
	PreservationNoticeHistoryID int IDENTITY(1,1) NOT NULL,
	SiteID uniqueidentifier NOT NULL,
	NewSiteID uniqueidentifier NULL,
	PreservationNoticeID  int NOT NULL,
	EventType  varchar(50) NOT NULL,
	PreservationNoticeName varchar(100) NOT NULL,
	DateEmailMessageCreated datetime NOT NULL,
	DateEmailMessageSent datetime NULL,
	DateAcknowledged datetime NULL,
	FromAddress varchar(500) NOT NULL,
	CustodianEmail varchar(500) NOT NULL,
	MessageSubject varchar(500) NOT NULL, 
	MessageBody text NOT NULL,
	Identifier varchar(500) NOT NULL,
	InterviewDate datetime NULL,
	CustodianID int NOT NULL,
	CustodianLastName varchar(200) NOT NULL,
	CustodianFirstName varchar(200) NOT NULL,
	HasAttachments bit DEFAULT(0) NOT NULL,
	VerificationTries int DEFAULT(0) NOT NULL
	)
ON [PRIMARY]
GO

CREATE TABLE dbo.PreservationNoticeHistoryAttachment 
	(
	PreservationNoticeHistoryAttachmentID int IDENTITY(1,1) NOT NULL,
	PreservationNoticeHistoryID int NOT NULL,
	Attachment image NOT NULL 
	) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[inetlog]    Script Date: 10/29/2007 8:11:37 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[inetlog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[inetlog]
GO

/****** Object:  Table [dbo].[inetlog]    Script Date: 10/29/2007 8:11:37 AM ******/
CREATE TABLE dbo.inetlog 
	(
	InetLogID int IDENTITY(1,1) NOT NULL,
	ClientHost varchar(255) NULL,
	username varchar(255) NULL,
	LogTime datetime NULL,
	service varchar(255) NULL,
	machine varchar(255) NULL,
	serverip varchar(50) NULL,
	processingtime int NULL,
	bytesrecvd int NULL,
	bytessent int NULL,
	servicestatus int NULL,
	win32status int NULL,
	operation varchar(255) NULL,
	target varchar(255) NULL,
	parameters varchar(255) NULL,
	PreservationNoticeHistoryID int NULL 
) 
ON [PRIMARY]
GO

/* Primary Keys */
IF OBJECT_ID ('PK_PreservationNoticeHistory', 'PK' ) IS NULL 
BEGIN
	ALTER TABLE dbo.PreservationNoticeHistory WITH NOCHECK ADD 
		CONSTRAINT [PK_PreservationNoticeHistory] PRIMARY KEY NONCLUSTERED 
		(
			PreservationNoticeHistoryID
		)  
	ON [PRIMARY] 
END
GO

ALTER TABLE dbo.PreservationNoticeHistoryAttachment WITH NOCHECK ADD 
	CONSTRAINT [PK_PreservationNoticeHistoryAttachment] PRIMARY KEY CLUSTERED 
	(
		PreservationNoticeHistoryAttachmentID
	)  
ON [PRIMARY] 
GO

ALTER TABLE dbo.UrlDictionary WITH NOCHECK ADD 
	CONSTRAINT [PK_UrlDictionary] PRIMARY KEY NONCLUSTERED 
		(
		UrlDictionaryID
		)
ON [PRIMARY] 
GO

ALTER TABLE dbo.inetlog WITH NOCHECK ADD 
	CONSTRAINT [PK_inetlog] PRIMARY KEY CLUSTERED 
		(
		InetLogID
		)
ON [PRIMARY] 
GO

/* Foreign Keys */
ALTER TABLE dbo.PreservationNoticeHistoryAttachment ADD 
	CONSTRAINT [FK_PreservationNoticeHistoryAttachment_PreservationNoticeHistory] FOREIGN KEY 
	(
		PreservationNoticeHistoryID
	) 
	REFERENCES dbo.PreservationNoticeHistory
	(
		PreservationNoticeHistoryID
	)
GO

/* Indexes */
CREATE CLUSTERED INDEX IDX_PreservationNoticeHistory_SiteID ON PreservationNoticeHistory (SiteID)
GO
CREATE NONCLUSTERED INDEX IDX_PreservationNoticeHistory_Identifier ON PreservationNoticeHistory (Identifier)
GO
CREATE NONCLUSTERED INDEX IDX_PreservationNoticeHistory_SiteID_PreservationNoticeID ON PreservationNoticeHistory (SiteID, PreservationNoticeID)
GO
CREATE NONCLUSTERED INDEX IDX_PreservationNoticeHistory_SiteID_PreservationNoticeID_CustodianID ON PreservationNoticeHistory (SiteID, PreservationNoticeID, CustodianID)
GO
CREATE CLUSTERED INDEX IDX_UrlDictionary_UrlKey ON UrlDictionary (UrlKey)
GO
CREATE NONCLUSTERED INDEX [IDX_logTime] ON [dbo].[inetlog]([LogTime]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_PreservationNoticeHistoryID] ON [dbo].[inetlog]([PreservationNoticeHistoryID]) ON [PRIMARY]
GO
CREATE  INDEX [idx_parameters] ON [dbo].[inetlog]([parameters]) ON [PRIMARY]
GO
CREATE  INDEX [idx_operation] ON [dbo].[inetlog]([operation]) ON [PRIMARY]
GO

